#!/usr/bin/env python
from lib.format_time import *
from lib.HandleMysql import *
from lib.switch_code import *
import sys
import re
reload(sys)
sys.setdefaultencoding('utf-8')

#mysql
def set_open_conn():    
    conn = HandleMysql('10.5.20.14','58885','ec_stat','L#c8UM5RGh5C','dbwww58com_groupbuy_open')
    conn.connect()
    return conn

#mysql - stats
def set_stats_conn():    
    conn = HandleMysql('10.5.20.16','58885','ec_stat','L#c8UM5RGh5C','dbwww58com_groupbuy_stats')
    conn.connect()
    return conn
#mysql - model
def set_model_conn():    
    conn = HandleMysql('groupbuyv2-s.db.58dns.org','58885','gupbuyuser_rdon',"ks*&dke12(*aws0",'dbwww58com_groupbuy_v2')
    conn.connect()
    return conn
#
def set_mid_data( date ):
    
    print "set_mid_data bigin : %s"%date
    
    conn = set_open_conn()
    stat_conn = set_stats_conn()    
    stat_table = "t_prodtype_stats"
    
    query = "truncate table t_prodtype_stats"
    conn.execute_sql(query)
    
    query1 = "SELECT city_name,cate1,avg(visits),avg(pay_order_count),avg(pay_amount) FROM `cate_city_metrics` where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(stat_date)  group by city_name,cate1"
    items1 = stat_conn.get_results(query1)
    
    for item in items1:
        city_name = item[0]
        cate_name = item[1]
        visits = item[2]
        orders = item[3]
        sales = item[4]
        
        param = [0,city_name,0,"",0,cate_name,sales,orders,visits]
        conn.insert_record(stat_table,param)
        
    query2 = "SELECT city_name,cate1,cate2,avg(visits),avg(pay_order_count),avg(pay_amount) FROM `cate_city_metrics` where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(stat_date)  group by city_name,cate2"
    items2 = stat_conn.get_results(query2)
    
    for item in items2:
        city_name = item[0]
        cate1_name = item[1]
        cate2_name = item[2]
        visits = item[3]
        orders = item[4]
        sales = item[5]
        
        param = [0,city_name,0,cate1_name,0,cate2_name,sales,orders,visits]
        conn.insert_record(stat_table,param)

    conn.close()
    stat_conn.close()
    print "set_mid_data end : %s"%date
    
def set_data_full():
    print "set_data_full bigin"
    
    conn = set_open_conn()
    query1 = "update t_prodtype_stats a left join cityinfo b on a.cityname=b.cityname set a.cityid=b.id"
    conn.execute_sql(query1)
    
    query2 = "update t_prodtype_stats a left join ptype b on a.typename=b.name set a.typeid=b.id"
    conn.execute_sql(query2)
    
    query3 = "update t_prodtype_stats a left join ptype b on a.ptypename=b.name set a.ptypeid=b.id"
    conn.execute_sql(query3)
    
    conn.close()
    print "set_data_full end"
    
def set_channel_data():
    print "set_channel_data bigin"
    
    #delele wanggou data
    conn = set_open_conn()
    query1 = "delete from t_prodtype_stats where typeid in (select id from ptype where channelid=3)"
    conn.execute_sql(query1)
    
    stat_conn = set_stats_conn()    
    stat_table = "t_prodtype_stats"
    
    query1 = "SELECT cate1,avg(visits),avg(pay_order_count),avg(pay_amount) FROM `channel_cate_metrics` where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(stat_date)  group by cate1"
    items1 = stat_conn.get_results(query1)
    
    for item in items1:
        city_name = ""
        cate_name = item[0]
        visits = item[1]
        if visits>0:
            visits = visits/10
        orders = item[2]
        if orders>0:
            orders = orders/10
        sales = item[3]
        if sales>0:
            sales = sales/10
        
        param = [364,city_name,0,"",0,cate_name,sales,orders,visits]
        conn.insert_record(stat_table,param)
        
    query2 = "SELECT cate1,cate2,avg(visits),avg(pay_order_count),avg(pay_amount) FROM `channel_cate_metrics` where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(stat_date)  group by cate2"
    items2 = stat_conn.get_results(query2)
    
    for item in items2:
        city_name = ""
        cate1_name = item[0]
        cate2_name = item[1]
        visits = item[2]
        if visits>0:
            visits = visits/10
        orders = item[3]
        if orders>0:
            orders = orders/10
        sales = item[4]
        if sales>0:
            sales = sales/10
        
        param = [364,city_name,0,cate1_name,0,cate2_name,sales,orders,visits]
        conn.insert_record(stat_table,param)
    
    query2 = "update t_prodtype_stats a left join ptype b on a.typename=b.name set a.typeid=b.id"
    conn.execute_sql(query2)
    
    query3 = "update t_prodtype_stats a left join ptype b on a.ptypename=b.name set a.ptypeid=b.id"
    conn.execute_sql(query3)
    
    conn.close()
    stat_conn.close()
    print "set_channel_data end"
    
def calculate_weight():
    
    print "calculate_weight bigin"
    
    stat_table = "t_prodtype_sort"
    LV1_SITE_ID = "5"
    LV2_SITE_ID = "6"
    
    MINVAL = 0
    MINORDERS = 0
    MINSALES = 0
    
    conn = set_open_conn()
    conn_model = set_model_conn()
    
    
    query_city = "select id from cityinfo order by id asc"
    items_city = conn_model.get_results(query_city)
    for city in items_city:
        city_id = city[0]
        
        query = "select code,defaultval from t_strategy where siteid=%s"%LV1_SITE_ID
        items = conn.get_results(query)
        for item in items:
            code = item[0]
            val = item[1]
            #print code
            if code == "minval":
                MINVAL = val
            elif code == "minorders":
                MINORDERS = val
            elif  code == "minsales":
                MINSALES = val
        query = "select id from prodtype where parentid=0"
        items_cate = conn_model.get_results(query)
        for cate in items_cate:
            cate_id=cate[0]
            query = "select cityid,ptypeid,typeid,sales,orders,visits from t_prodtype_stats where cityid in (364,%s) and typeid=%s"%(city_id,cate_id)
           
            items = conn.get_results(query)
            if len(items)>0:
                for item in items:
                    cityid = city_id
                    #print cityid
                    ptypeid = item[1]
                    typeid = item[2]
                    sales = item[3]
                    orders = item[4]
                    visits = item[5]
                    
                    if ptypeid==None:
                        ptypeid=0
                    
                    #wr = 0
                    #print "calculating"
                    wr = 0.0
                    sum_mod = sales + orders+visits;
                    if sales > 0:
                        wr = (sales/(sales + visits)) * MINSALES
                    if orders> 0:
                        wr = wr + (orders/orders+visits)* MINORDERS
                    if visits > 0:
                        wr = wr + (visits/sum_mod) * MINVAL

                    query_in = "select count(1) from %s where cityid=%s and typeid=%s"%(stat_table,cityid,typeid)
                    items_in = conn.get_results(query_in)
                    cnt = items_in[0][0]
                    if cnt > 0:
                        update_sql = "update %s set weight =%s where cityid=%s and typeid=%s"%(stat_table,wr,cityid,typeid)
                        conn.execute_sql(update_sql)
                    else:
                        param = [cityid,ptypeid,typeid,wr]
                        #print param
                        conn.insert_record(stat_table,param)
            else:
                #pass
                query_in="select count(1) from %s where cityid=%s and typeid=%s"%(stat_table,city_id,cate_id)
                items_in = conn.get_results(query_in)
                cnt = items_in[0][0]
                if cnt == 0:
                    param = [city_id,0,cate_id,MINVAL]
                    #print param
                    conn.insert_record(stat_table,param)
            
        # level 2 cate
            
        query = "select id,parentid from prodtype where parentid>0"
        items_cate = conn_model.get_results(query)
        for cate in items_cate:
            cate_id=cate[0]
            pid=cate[1]
            
            query = "select code,defaultval from t_strategy where siteid=%s"%LV2_SITE_ID
            items = conn.get_results(query)
            for item in items:
                code = item[0]
                val = item[1]
                
                if code == "minval":
                    MINVAL = val
                elif code == "minorders":
                    MINORDERS = val
                elif  code == "minsales":
                    MINSALES = val
            
            query = "select cityid,ptypeid,typeid,sales,orders,visits from t_prodtype_stats where cityid in (364,%s) and typeid=%s"%(city_id,cate_id)

            items = conn.get_results(query)
            if len(items)>0:
                for item in items:
                    cityid = city_id
                    ptypeid = item[1]
                    typeid = item[2]
                    sales = item[3]
                    orders = item[4]
                    visits = item[5]
                    if ptypeid==None:
                        ptypeid=0
                    wr = 0.0
                    sum_mod = sales + orders+visits;
                    if sales > 0:
                        wr = (sales/(sales + orders)) * MINSALES
                    if orders> 0:
                        wr = wr + (orders/orders+visits)* MINORDERS
                    if visits > 0:
                        wr = wr + (visits/sum_mod) * MINVAL
                
                    query_in = "select count(1) from %s where cityid=%s and typeid=%s"%(stat_table,cityid,typeid)
                    items_in = conn.get_results(query_in)
                    cnt = items_in[0][0]
                    if cnt > 0:
                        update_sql = "update %s set weight =%s where cityid=%s and typeid=%s"%(stat_table,wr,cityid,typeid)
                        conn.execute_sql(update_sql)
                    else:
                        param = [cityid,ptypeid,typeid,wr]
                        conn.insert_record(stat_table,param)
            else:
                query_in = "select count(1) from %s where cityid=%s and typeid=%s"%(stat_table,city_id,cate_id)
                items_in = conn.get_results(query_in)
                cnt = items_in[0][0]
                if cnt == 0:
                    param = [city_id,0,cate_id,MINVAL]
                    #print param
                    conn.insert_record(stat_table,param)
    conn.close()
    conn_model.close()
    print "calculate_weight end"

#logic
def execute(date):

    try:
        set_mid_data( date )
        set_data_full()
        set_channel_data()
        calculate_weight()
    except Exception,e:
        print 'error:',e
#
def main():

   #date = '2012-05-28'
   #execute(date)   
   if len(sys.argv)==1:
        start_date=get_yesterday()
        end_date=start_date
   elif len(sys.argv)==2:
        start_date=format_date(sys.argv[1])
        end_date=start_date
   elif len(sys.argv)==3:
        start_date=format_date(sys.argv[1])
        end_date=format_date(sys.argv[2])
   stat_date=start_date
   while stat_date>=start_date and stat_date<=end_date:
        print "The program of the date of %s is beginning at %s " %(stat_date,get_now())
        #
        execute( stat_date)
        #set_channel_data()
        #calculate_weight()
        print "The program of the date of %s is end at %s " %(stat_date,get_now())
        stat_date=var_date(stat_date)


if __name__=='__main__':
    main()
